﻿using System;
using System.Collections.Generic;
using System.Text;
using MySql.Data.MySqlClient;
using System.Data;
using BCMS.DAL.IDAL;
using BCMS.Business.DataContract;

namespace BCMS.DAL.MySQL
{
    public class Coupons : ICoupon
    {
        private MySqlConnection _internalConnection;
        private MySqlTransaction _internalADOTransaction = null;

        public void BeginADOTransaction()
        {
            if (_internalConnection.State != System.Data.ConnectionState.Open)
                _internalConnection.Open();
            _internalADOTransaction = _internalConnection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
        }
        public void RollBackTransaction()
        {
            _internalADOTransaction.Rollback();
            _internalADOTransaction = null;
        }
        public void CommitADOTransaction()
        {
            _internalADOTransaction.Commit();
            _internalADOTransaction = null;
        }
        public void Open(string connString)
        {
            if (_internalConnection == null)
                _internalConnection = new MySqlConnection(connString);
            if (_internalConnection.State != ConnectionState.Open)
                _internalConnection.Open();
        }
        public void Close()
        {
            if (_internalConnection != null && _internalConnection.State != System.Data.ConnectionState.Closed)
                _internalConnection.Close();
        }

        #region  Params
        private const string PARM_SERIAL = "@SERIAL";
        private const string PARM_STARTDATE = "@STARTDATE";
        private const string PARM_EXPIREDATE = "@EXPIREDATE";
        private const string PARM_PARVALUE = "@PARVALUE";
        private const string PARM_STATUS = "@STATUS";
        private const string PARM_NOTE = "@NOTE";
        private const string PARM_RELEASEID = "@RELEASEID";
        private const string PARM_CLAIMBY = "@CLAIMBY";
        private const string PARM_ISCANCELED = "@ISCANCEL";
        private const string PARM_CLAIMDATE = "@CLAIMDATE";
        #endregion

        #region SQL string
        private const string COUPON_INSERT = "INSERT INTO coupons (SERIAL, STARTDATE, EXPIREDATE, PARVALUE, STATUS, NOTE, RELEASEID, ISCANCEL) VALUES (@SERIAL,@STARTDATE, @EXPIREDATE, @PARVALUE, @STATUS, @NOTE, @RELEASEID, @ISCANCEL); SELECT LAST_INSERT_ID();";

        private const string GET_BYCLAIM = "SELECT * FROM coupons WHERE (CLAIMBY=@CLAIMBY) AND (CLAIMDATE BETWEEN @FROM AND @TO)";

        private const string COUPON_GET = "SELECT * FROM coupons WHERE ('1'='1') ";

        private const string COUPON_GETBYSERIAL = "SELECT * FROM coupons WHERE SERIAL=@SERIAL";

        private const string COUPON_DELETE_BYSERIAL = "DELETE FROM coupons WHERE SERIAL=@SERIAL";

        private const string COUPON_DELETE_BYRELEASEID = "DELETE FROM coupons WHERE RELEASEID=@RELEASEID";

        private const string COUPON_CLAIM = "UPDATE coupons SET STATUS='2', CLAIMBY = @CLAIMBY, NOTE=@NOTE , CLAIMDATE = @CLAIMDATE, LASTUPDATED=CURRENT_TIMESTAMP WHERE SERIAL=@SERIAL";

        private const string COUPON_EXTEND = "UPDATE coupons SET EXPIREDATE=@EXPIREDATE, NOTE=@NOTE WHERE SERIAL=@SERIAL";

        private const string COUPON_UNDOCLAIM = "UPDATE coupons SET STATUS='1', CLAIMBY=NULL, CLAIMDATE=NULL WHERE SERIAL=@SERIAL";

        private const string DISPOSE_BYRELEASEID = "UPDATE coupons SET STATUS=@status WHERE STATUS=@normalstatus AND RELEASEID=@RELEASEID";

        private const string COUPON_CANCEL_BYSERIAL = "UPDATE coupons SET STATUS=3 WHERE STATUS=1 AND SERIAL=@SERIAL";

        private const string COUPON_DELETE = "UPDATE coupons SET CANCEL=1 WHERE SERIAL=@SERIAL";

        private const string COUPON_COUNT_BYRELEASE = "SELECT COUNT(*) FROM coupons WHERE RELEASED=@RELEASED";

        private const string COUPON_RECONSILE = "UPDATE coupons SET STATUS='EXPIRED' WHERE EXPIREDATE<@EXPIREDATE";

        private const string COUPON_GETBYRELEASE = "SELECT * FROM coupons WHERE RELEASEID=@RELEASEID";

        private const string COUPON_SEARCH = "SELECT * FROM coupons WHERE '1' = '1' ";

        private const string COUPON_SUMMARY_BYVALUE = "SELECT value, releaseId, status, count(serial) as Qtty, sum(value) as TotalValue FROM coupons WHERE value=@value GROUP BY releaseid, status ";

        private const string COUPON_SUMMARY_BYRELEASE = "SELECT value, releaseId, status, count(serial) as Qtty, sum(value) as TotalValue FROM coupons WHERE releaseid=@releaseid GROUP BY value, status ";

        private const string COUPON_SUMMARY_BYSTATUS = "SELECT value, releaseId, status, count(serial) as Qtty, sum(value) as TotalValue FROM coupons WHERE status=@status GROUP BY releaseid, value ";

        private const string COUPON_SUMMARY = "SELECT value, releaseId, status, count(serial) as Qtty, sum(value) as TotalValue FROM coupons GROUP BY releaseid, value, status ";

        private const string REPORT_EXPIRED_UNCLAIM_DETAIL = "SELECT * FROM COUPONS WHERE (EXPIREDATE<@REPORTDATE) AND (CLAIMBY IS NULL) AND (STARTDATE BETWEEN @FROMDATE AND @TODATE)";

        private const string REPORT_UNEXPIRE_UNCLAIM_DETAIL = "SELECT * FROM COUPONS WHERE (EXPIREDATE>@REPORTDATE) AND (CLAIMBY IS NOT NULL) AND (STARTDATE BETWEEN @FROMDATE AND @TODATE)";

        private const string GET_CLAIMEDCOUPONS = "SELECT PARVALUE, CLAIMBY, CLAIMDATE, COUNT(PARVALUE)  QTTY, COUNT(PARVALUE)*PARVALUE  TOTALVALUE FROM coupons WHERE (CLAIMBY IS NOT NULL) AND (CLAIMDATE BETWEEN @FROMDATE AND @TODATE) GROUP BY PARVALUE, CLAIMBY, CLAIMDATE";

        private const string REPORT_CLAIMBYUSER = "SELECT count(PARVALUE) QTTY, PARVALUE, count(PARVALUE)*PARVALUE TOTALVALUE FROM COUPONS WHERE (CLAIMBY=@CLAIMBY) AND (CLAIMDATE BETWEEN @FROMDATE AND @TODATE) group by PARVALUE";

        private const string REPORT_EXPIRED = "SELECT count(PARVALUE) QTTY, PARVALUE, count(PARVALUE)*PARVALUE TOTALVALUE FROM COUPONS WHERE (EXPIREDATE<@REPORTDATE) AND (CLAIMBY IS NULL) group by PARVALUE";

        private const string REPORT_UNEXPIRED = "SELECT count(PARVALUE) QTTY, PARVALUE, count(PARVALUE)*PARVALUE TOTALVALUE FROM COUPONS WHERE (EXPIREDATE>@REPORTDATE) AND (CLAIMBY IS NULL) AND (STATUS=1)  group by PARVALUE";

        private const string REPORT_RELEASEDATE = "SELECT count(PARVALUE) QTTY, PARVALUE, count(PARVALUE)*PARVALUE TOTALVALUE  FROM COUPONS WHERE STARTDATE BETWEEN @FROMDATE AND @TODATE group by PARVALUE";
            
        private const string FIND_CLAIMBYSERIAL = "SELECT * FROM COUPONS WHERE (LASTUPDATED>=@smaller) AND (LASTUPDATED<=@greater) AND (CLAIMBY=@claimby) order by lastupdated desc";

        #endregion

        public int insertCoupon(string serial, DateTime start, DateTime expire, decimal parValue, int status, string note, int releaseId, bool isCanceled)
        {
            try
            {
                //Get parameter from cache
                MySqlParameter[] parms = getInsertCouponParameters();

                parms[0].Value = serial;
                parms[1].Value = start;
                parms[2].Value = expire;
                parms[3].Value = parValue;
                parms[4].Value = status;
                parms[5].Value = note;
                parms[6].Value = releaseId;
                parms[7].Value = isCanceled;


                return MyHelper.ExecuteScalar(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_INSERT, parms);

            }
            catch
            {
                throw;
            }
        }

        public List<CouponInfo> getCouponsByClaim(int? claimBy, string serial, DateTime from, DateTime to)
        {
            MySqlDataReader rdr = null;
            List<CouponInfo> list = new List<CouponInfo>();
            try
            {
                StringBuilder sb = new StringBuilder(GET_BYCLAIM);
                if (!serial.Equals("-"))
                    sb.Append(" AND (serial='" + serial + "')");

                MySqlParameter[] parms = new MySqlParameter[] {  
                                            new MySqlParameter("@CLAIMBY", MySqlDbType.Int32),
                                            new MySqlParameter("@FROM", MySqlDbType.DateTime),
                                            new MySqlParameter("@TO", MySqlDbType.DateTime)
                };
                parms[0].Value = (object)claimBy ?? DBNull.Value;
                parms[1].Value = from;
                parms[2].Value = to;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, sb.ToString(), parms);

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public List<CouponInfo> getCoupons(string serial, int status)
        {
            MySqlDataReader rdr = null;
            List<CouponInfo> list = new List<CouponInfo>();
            try
            {
                StringBuilder sb = new StringBuilder(COUPON_SEARCH);
                sb.Append(searchBySerial(serial));
                sb.Append(searchByStatus(status));

                rdr = MyHelper.ExecuteReaderNoParm(_internalConnection, _internalADOTransaction, CommandType.Text, sb.ToString());
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));
                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public int claimCouponBySerial(string serial, int userid, string note, DateTime claimDate)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{ 
                                        new MySqlParameter(PARM_SERIAL, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_CLAIMBY, MySqlDbType.Int32),
                                        new MySqlParameter(PARM_CLAIMDATE, MySqlDbType.DateTime)
                };
                parms[0].Value = serial;
                parms[1].Value = note;
                parms[2].Value = userid;
                parms[3].Value = claimDate;

                return MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_CLAIM, parms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int extendCouponBySerial(string serial, string note, DateTime expireDate)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{ 
                                        new MySqlParameter(PARM_SERIAL, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar),
                                        new MySqlParameter(PARM_EXPIREDATE, MySqlDbType.DateTime)
                };
                parms[0].Value = serial;
                parms[1].Value = note;
                parms[2].Value = expireDate;

                return MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_EXTEND, parms);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int undoClaimCouponBySerial(string serial)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_SERIAL, MySqlDbType.VarChar);
                parm.Value = serial;

                return MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_UNDOCLAIM, parm);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public CouponInfo getCouponBySerial(string serial)
        {
            MySqlDataReader rdr = null;
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_SERIAL, MySqlDbType.VarChar);
                parm.Value = serial;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_GETBYSERIAL, parm);

                if (rdr.HasRows)
                {
                    rdr.Read();
                    return ConvertToObject(rdr);
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public void deleteCouponBySerial(string serial)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_SERIAL, MySqlDbType.String);
                parm.Value = serial;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_DELETE_BYSERIAL, parm);
            }
            catch
            {
                throw;
            }
        }

        public void deleteCouponByReleaseId(int releaseId)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_RELEASEID, MySqlDbType.Int32);
                parm.Value = releaseId;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_DELETE_BYRELEASEID, parm);
            }
            catch
            {
                throw;
            }
        }

        public void disposelCouponByReleaseId(int releaseId, int disposeStatus, int normalStatus)
        {
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{ 
                        new MySqlParameter(PARM_RELEASEID, MySqlDbType.Int32),
                        new MySqlParameter(PARM_STATUS, MySqlDbType.Int32),
                        new MySqlParameter("@normalStatus", MySqlDbType.Int32)
                };

                parms[0].Value = releaseId;
                parms[1].Value = disposeStatus;
                parms[2].Value = normalStatus;

                MyHelper.ExecuteNonQuery(_internalConnection, _internalADOTransaction, CommandType.Text, DISPOSE_BYRELEASEID, parms);

            }
            catch
            {
                throw;
            }
        }

        public void cancelCouponBySerial(string serial)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_SERIAL, MySqlDbType.VarChar);
                parm.Value = serial;

                MyHelper.ExecuteNonQuerySingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_CANCEL_BYSERIAL, parm);

            }
            catch
            {
                throw;
            }
        }

        public int countCouponByRelease(int releaseId)
        {
            try
            {
                MySqlParameter parm = new MySqlParameter(PARM_RELEASEID, MySqlDbType.Int32);
                parm.Value = releaseId;

                return Convert.ToInt32(MyHelper.ExecuteScalarSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, COUPON_COUNT_BYRELEASE, parm));

            }
            catch
            {
                throw;
            }

        }

        public List<CouponInfo> reportExpiredUnClaimed(DateTime fromDate, DateTime toDate, DateTime reportDate)
        {
            MySqlDataReader rdr = null;
            List<CouponInfo> list = new List<CouponInfo>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                                            new MySqlParameter("@FROMDATE", MySqlDbType.Date),
                                            new MySqlParameter("@TODATE", MySqlDbType.Date),
                                            new MySqlParameter("@REPORTDATE", MySqlDbType.Date)
                };
                parms[0].Value = fromDate;
                parms[1].Value = toDate;
                parms[2].Value = reportDate;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, REPORT_EXPIRED_UNCLAIM_DETAIL, parms);

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        private CouponInfo ConvertToObject(MySqlDataReader rdr)
        {
            CouponInfo item = new CouponInfo();
            item.Serial = rdr["SERIAL"].ToString();
            item.StartDate = Convert.ToDateTime(rdr["STARTDATE"]);
            item.ExpireDate = Convert.ToDateTime(rdr["EXPIREDATE"]);
            item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
            item.Status = Convert.ToInt16(rdr["STATUS"]);
            item.Note = rdr["NOTE"].ToString();
            item.ReleaseId = Convert.ToInt32(rdr["RELEASEID"]);
            item.ClaimBy = (rdr["CLAIMBY"] != DBNull.Value) ? Convert.ToInt32(rdr["CLAIMBY"]) : (int?)null;
            item.IsCanceled = Convert.ToBoolean(rdr["ISCANCEL"]);
            item.ClaimDate = (rdr["CLAIMDATE"] != DBNull.Value) ? Convert.ToDateTime(rdr["CLAIMDATE"]) : (DateTime?)null;
            item.LastUpdated = Convert.ToDateTime(rdr["LASTUPDATED"]);
            return item;
        }

        public List<CouponInfo> findClaimedCouponsBySeries(DateTime smaller, DateTime greater, int claimby)
        {
            MySqlDataReader rdr = null;
            List<CouponInfo> list = new List<CouponInfo>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[] {  
                                            new MySqlParameter("@CLAIMBY", MySqlDbType.Int32),
                                            new MySqlParameter("@SMALLER", MySqlDbType.DateTime),
                                            new MySqlParameter("@GREATER", MySqlDbType.DateTime)
                };
                parms[0].Value = claimby;
                parms[1].Value = smaller;
                parms[2].Value = greater;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text,FIND_CLAIMBYSERIAL, parms);

                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        #region Report
        public List<CouponBrief> reportClaimedCoupons(DateTime fromDate, DateTime toDate)
        {
            MySqlDataReader rdr = null;
            List<CouponBrief> list = new List<CouponBrief>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                    new MySqlParameter("@FROMDATE", MySqlDbType.Date),
                    new MySqlParameter("@TODATE", MySqlDbType.Date)
                };
                parms[0].Value = fromDate;
                parms[1].Value = toDate;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, GET_CLAIMEDCOUPONS, parms);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        CouponBrief item = new CouponBrief();
                        item.ClaimBy = Convert.ToInt32(rdr["CLAIMBY"]);
                        item.ClaimDate = Convert.ToDateTime(rdr["CLAIMDATE"]);
                        item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
                        item.TotalQtty = Convert.ToInt64(rdr["QTTY"]);
                        item.TotalValue = Convert.ToDecimal(rdr["TOTALVALUE"]);

                        list.Add(item);
                    }

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public List<CouponBrief> reportClaimedCouponsByUser(int claimBy, DateTime fromDate, DateTime toDate)
        {
            MySqlDataReader rdr = null;
            List<CouponBrief> list = new List<CouponBrief>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                    new MySqlParameter("@CLAIMBY", MySqlDbType.Int32),
                    new MySqlParameter("@FROMDATE", MySqlDbType.Date),
                    new MySqlParameter("@TODATE", MySqlDbType.Date)
                };
                parms[0].Value = claimBy;
                parms[1].Value = fromDate;
                parms[2].Value = toDate;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, REPORT_CLAIMBYUSER, parms);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        CouponBrief item = new CouponBrief();
                        item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
                        item.TotalQtty = Convert.ToInt64(rdr["QTTY"]);
                        item.TotalValue = Convert.ToDecimal(rdr["TOTALVALUE"]);

                        list.Add(item);
                    }

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public List<CouponBrief> reportExpireCoupons(DateTime reportDate)
        {
            MySqlDataReader rdr = null;
            List<CouponBrief> list = new List<CouponBrief>();
            try
            {
                MySqlParameter parm = new MySqlParameter("@REPORTDATE", MySqlDbType.Date);
                parm.Value = reportDate;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, REPORT_EXPIRED, parm);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        CouponBrief item = new CouponBrief();
                        item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
                        item.TotalQtty = Convert.ToInt64(rdr["QTTY"]);
                        item.TotalValue = Convert.ToDecimal(rdr["TOTALVALUE"]);

                        list.Add(item);
                    }

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public List<CouponBrief> reportUnExpireCoupons(DateTime reportDate)
        {
            MySqlDataReader rdr = null;
            List<CouponBrief> list = new List<CouponBrief>();
            try
            {
                MySqlParameter parm = new MySqlParameter("@REPORTDATE", MySqlDbType.Date);
                parm.Value = reportDate;

                rdr = MyHelper.ExecuteReaderSingleParm(_internalConnection, _internalADOTransaction, CommandType.Text, REPORT_UNEXPIRED, parm);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        CouponBrief item = new CouponBrief();
                        item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
                        item.TotalQtty = Convert.ToInt64(rdr["QTTY"]);
                        item.TotalValue = Convert.ToDecimal(rdr["TOTALVALUE"]);

                        list.Add(item);
                    }

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        public List<CouponBrief> reportCouponsByReleaseDate(DateTime fromDate, DateTime toDate)
        {
            MySqlDataReader rdr = null;
            List<CouponBrief> list = new List<CouponBrief>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                    new MySqlParameter("@FROMDATE", MySqlDbType.Date),
                    new MySqlParameter("@TODATE", MySqlDbType.Date)
                };
                parms[0].Value = fromDate;
                parms[1].Value = toDate;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, REPORT_RELEASEDATE, parms);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                    {
                        CouponBrief item = new CouponBrief();
                        item.ParValue = Convert.ToDecimal(rdr["PARVALUE"]);
                        item.TotalQtty = Convert.ToInt64(rdr["QTTY"]);
                        item.TotalValue = Convert.ToDecimal(rdr["TOTALVALUE"]);

                        list.Add(item);
                    }

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        #endregion

        #region SearchBuilder

        private string searchBySerial(string serial)
        {
            if (serial.Equals("-"))
                return string.Empty;
            else
                return " AND SERIAL='" + serial + "'";
        }

        private string searchByStatus(int status)
        {
            if (status.Equals(0))
                return string.Empty;
            else
                return " AND STATUS = " + status.ToString();
        }

        #endregion

        #region ParamBuilder
        private MySqlParameter[] getInsertCouponParameters()
        {
            MySqlParameter[] parms = MyHelper.GetCacheParameters(COUPON_INSERT);

            if (parms == null)
            {
                parms = new MySqlParameter[]{
                            new MySqlParameter(PARM_SERIAL, MySqlDbType.VarChar, 20),
                            new MySqlParameter(PARM_STARTDATE, MySqlDbType.DateTime),
                            new MySqlParameter(PARM_EXPIREDATE, MySqlDbType.DateTime),
                            new MySqlParameter(PARM_PARVALUE, MySqlDbType.Decimal),
                            new MySqlParameter(PARM_STATUS, MySqlDbType.Int16, 5),
                            new MySqlParameter(PARM_NOTE, MySqlDbType.VarChar, 200),
                            new MySqlParameter(PARM_RELEASEID, MySqlDbType.Int32),
                            new MySqlParameter(PARM_ISCANCELED, MySqlDbType.Bit)
                };

                MyHelper.CacheParameters(COUPON_INSERT, parms);
            }
            return parms;
        }

        #endregion

        #region Update 6/1/2012

        public List<CouponInfo> reportUnexpiredUnclaimed(DateTime fromDate, DateTime toDate, DateTime reportDate)
        {
            MySqlDataReader rdr = null;
            List<CouponInfo> list = new List<CouponInfo>();
            try
            {
                MySqlParameter[] parms = new MySqlParameter[]{
                                            new MySqlParameter("@FROMDATE", MySqlDbType.Date),
                                            new MySqlParameter("@TODATE", MySqlDbType.Date),
                                            new MySqlParameter("@REPORTDATE", MySqlDbType.Date)
                };
                parms[0].Value = fromDate;
                parms[1].Value = toDate;
                parms[2].Value = reportDate;

                rdr = MyHelper.ExecuteReader(_internalConnection, _internalADOTransaction, CommandType.Text, REPORT_UNEXPIRE_UNCLAIM_DETAIL, parms);
                if (rdr.HasRows)
                {
                    while (rdr.Read())
                        list.Add(ConvertToObject(rdr));

                    return list;
                }
                else
                    return null;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (rdr != null)
                    rdr.Close();
            }
        }

        #endregion
    }
}
